import { drizzle } from "drizzle-orm/d1";
import { and, eq } from "drizzle-orm";
import { nanoid } from 'nanoid';
import { tableSchemas } from "./index";

type IParams = Record<string, any>;
type ITable = keyof typeof tableSchemas;

export async function getAllContent(db: D1Database) {
  const { results } = await db.prepare("SELECT * FROM users").all();
  return results;
}

export async function getUserInfo(d1: D1Database, where: IParams) {
  const db = drizzle(d1);
  const users = tableSchemas.users.table;
  return await db
    .select({
      id: users.id,
      username: users.username,
      email: users.email,
      email_verified: users.email_verified,
      phone: users.phone,
      phone_verified: users.phone_verified,
      push_wx: users.push_wx,
      push_tg: users.push_tg,
      push_web: users.push_web,
      role: users.role,
      plan: users.plan,
    })
    .from(users)
    .where(eq(users.email, where.email))
    // .leftJoin()
    .run();
}

export async function getD1DataByTable(
  d1: D1Database,
  table: string,
  params: IParams
) {
  const sql = generateSelectSql(table, params);
  return await d1.prepare(sql).first();
}

export async function getD1DataListByTable(
  d1: D1Database,
  table: string,
  params: IParams
) {
  const sql = generateSelectSql(table, params);
  const { results } = await d1.prepare(sql).all();
  return params.id ? results[0] : results;
}

export function generateSelectSql(table: string, params: IParams) {
  let whereClause = "";
  let sortBySyntax = "";
  let limitSyntax: string = "";
  let offsetSyntax = "";

  if (params.id) {
    whereClause = `WHERE id = '${params.id}'`;
  } else {
    let { limit, offset, filters } = params;

    sortBySyntax = sortClauseBuilder(params);

    limit = limit ?? 0;
    limitSyntax = limit > 0 ? `limit ${limit}` : "";
    // console.log("limitSyntax ==>", limitSyntax);

    offset = offset ?? 0;
    offsetSyntax = offset > 0 ? `offset ${offset}` : "";

    whereClause = whereClauseBuilder(filters);
  }

  let sql = `SELECT *, COUNT() OVER() AS total FROM ${table} ${whereClause} ${sortBySyntax} ${limitSyntax} ${offsetSyntax}`;
  sql = sql.replace(/\s+/g, " ").trim() + ";";

  console.log("sql ==>", sql);
  return sql;
}

export async function getD1ByTableAndId(
  d1: D1Database,
  table: string,
  id: string
) {
  const { results } = await d1
    .prepare(`SELECT * FROM ${table} where id = '${id}';`)
    .all();

  return results[0];
}

export function prepareD1Data(data: IParams, tbl = "") {
  const table = data.table || tbl;
  const schema = getRepoFromTable(table);
  const now = new Date().getTime();
  data.created_on = now;
  data.updated_on = now;
  delete data.table;

  if (!schema.id) {
    delete data.id;
  }
  return data;
}

export async function insertD1Data(
  d1: D1Database,
  table: ITable,
  data: IParams
) {
  const db = drizzle(d1);
  data = prepareD1Data(data, table);
  const schema = getRepoFromTable(table);
  try {
    // let sql = db.insert(schema).values(data).getSQL();
    // @ts-ignore
    const result = await db.insert(schema).values(data).returning().get();
    return result;
  } catch (error) {
    return error;
  }
}

export async function deleteD1ByTableAndId(
  d1: D1Database,
  table: ITable,
  id: string
) {
  const db = drizzle(d1);

  const schema = getRepoFromTable(table);

  const result = await db.delete(schema).where(eq(schema.id, id)).run();

  return result;
}

export async function updateD1Data(
  d1: D1Database,
  table: ITable,
  data: IParams,
  params?: IParams
) {
  const db = drizzle(d1);
  const schemaTable = table ?? data.table;
  const repo = getRepoFromTable(schemaTable);
  const recordId = data.id;
  if (data.data && data.data.id) {
    delete data.data.id;
  }

  const now = new Date().getTime();
  data.data.updated_on = now;

  const eqArgs = [eq(repo.id, recordId)];
  if (params) {
    for (const key in params) {
      if (key !== "id") {
        // @ts-ignore
        eqArgs.push(eq(repo[key], params[key]));
      }
    }
  }
  const result = await db
    .update(repo)
    .set(data.data)
    .where(and(...eqArgs))
    .returning({ id: repo.id })
    .values();

  const id = result && result[0] ? result[0]["0"] : undefined;

  console.log("updating data result ", result);

  return id ? { id } : result;
}

export function getSchemaFromTable(tableName: ITable) {
  return tableSchemas[tableName].definition;
}

export function getRepoFromTable(tableName: ITable) {
  return tableSchemas[tableName].table;
}

export function sortClauseBuilder(params: { sort?: string[] }) {
  let sortClause = "";

  if (params.sort) {
    sortClause =
      "order by " + params.sort.join(", ").replace(new RegExp(":", "g"), " ");
  }

  return sortClause;
}

export function whereClauseBuilder(filters: any) {
  let whereClause = "";

  if (!filters || Object.keys(filters).length === 0) {
    return whereClause;
  }

  let AND = "";
  whereClause = "WHERE";
  for (const key of Object.keys(filters)) {
    const filter = filters[key];
    const condition = Object.keys(filter)[0];
    // if (typeof filter === 'string') {
    //   if (filter.toLowerCase().includes('is')) {
    //     whereClause = `${whereClause} ${AND} ${key} ${filter}`;
    //   } else {
    //     whereClause = `${whereClause} ${AND} ${key} = '${filter}'`;
    //   }
    // } else {
    //   whereClause = `${whereClause} ${AND} ${key} = ${filter}`;
    // }
    whereClause = `${whereClause} ${AND} ${key} ${processCondition(
      condition
    )} '${filter[condition]}'`;

    AND = "AND";
  }
  return whereClause;
}

export function processCondition(condition: string) {
  switch (condition) {
    case "$eq":
      return "=";

    default:
      break;
  }
}
export async function getRecords(
  d1: D1Database,
  table: ITable,
  params: IParams,
  customDataFunction: undefined | Function = undefined
): Promise<{ data: any; source: string; total: number; contentType?: any }> {
  let d1Data;
  let total = 0;

  if (customDataFunction) {
    d1Data = await customDataFunction();
    if (d1Data && d1Data[0]) {
      total = d1Data[0].total;
    } else if (Object.keys(d1Data).length) {
      total = 1;
    }
  } else {
    d1Data = await getD1DataByTable(d1, table, params);
  }

  if (d1Data?.length) {
    total = d1Data[0].total;
  } else if (d1Data) {
    total = 1;
    d1Data.total = undefined;
  }

  return { data: d1Data, source: "d1", total };
}

export async function insertRecord(d1: D1Database, data: IParams) {
  const content = data;
  const id = nanoid();
  content.data.id = id;
  let error = "";

  try {
    const result = await insertD1Data(d1, content.table, content.data);
    return { code: 201, data: result };
  } catch (err) {
    error =
      "error posting content " +
      content.data.table +
      err +
      JSON.stringify(content.data, null, 2);
  }
  return { code: 500, error };
}

export async function updateRecord(
  d1: D1Database,
  data: IParams,
  params: IParams
) {
  try {
    const result = await updateD1Data(d1, data.table, data, params);
    return { code: 200, data: result };
  } catch (error) {
    console.log("error posting content", error);
    return { code: 500, message: error };
  }
}

export async function deleteRecord(d1: D1Database, data: IParams) {
  try {
    await deleteD1ByTableAndId(d1, data.table, data.id);
  } catch (error) {
    console.log("error deleting content", error);
    return { code: 500, message: error };
  }
}
